﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace EA.DAO
{
    public abstract class ObjectDA
    {

        private String connection_string;

        /// <summary>
        ///Init Object
        /// </summary>
        public ObjectDA(String connection_string)
        {
            this.connection_string = connection_string;
        }

        /// <summary>
        /// Init Connect
        /// </summary>
        /// <returns>Boolean</returns>
        protected SqlConnection GetConnection()
        {
            SqlConnection conn = new SqlConnection(this.connection_string);
            conn.Open();
            if (conn.State == ConnectionState.Open)
                return conn;
            else
                return null;
        }

        /// <summary>
        /// Close Connect
        /// </summary>
        public void CloseConnection(SqlConnection conn)
        {
            if (conn!=null&&conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Fill Table
        /// </summary>
        /// <param name="command">String</param>
        /// <param name="param">SqlParameter[]</param>
        /// <param name="dt">DataTable</param>
        /// <returns>int</returns>
        public void Fill(String command, SqlParameter[] param, DataTable dt)
        {
            SqlConnection conn = null;
            try
            {
                conn = this.GetConnection();
                if (conn != null)
                {
                    SqlCommand cmd = new SqlCommand(command, conn);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    this.MappingParameters(cmd.Parameters, param);
                    adapter.SelectCommand = cmd;
                    adapter.SelectCommand.CommandType = CommandType.Text;
                    adapter.Fill(dt);
                }
            }
            finally
            {
                CloseConnection(conn);
            }
        }

        /// <summary>
        /// Fill Table
        /// </summary>
        /// <param name="command">String</param>
        /// <param name="dt">DataTable</param>
        /// <returns>int</returns>
        public void Fill(String command, DataTable dt)
        {
            SqlConnection conn = null;
            try
            {
                conn = this.GetConnection();
                if (conn != null)
                {
                    SqlCommand cmd = new SqlCommand(command, conn);
                    this.MappingParameters(cmd.Parameters, null);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.SelectCommand.CommandType = CommandType.Text;
                    adapter.Fill(dt);
   
                }
            }
            finally
            {
                CloseConnection(conn);
            }
        }

        /// <summary>
        /// Execute Query
        /// </summary>
        /// <param name="command">String</param>
        /// <param name="param">SqlParameter[]</param>
        /// <returns>Boolean</returns>
        public Boolean ExecuteNonQuery(String command, SqlParameter[] param)
        {
            SqlConnection conn=null;
            try
            {
                conn = this.GetConnection();
                if (conn != null)
                {
                    SqlCommand cmd = new SqlCommand(command, conn);
                    this.MappingParameters(cmd.Parameters, param);
                    cmd.CommandType = CommandType.Text;
                    if (cmd.ExecuteNonQuery() == 0)
                        return false;
                    else
                        return true;

                }
            }
            finally
            {
                CloseConnection(conn);
            }
            return false;
        }

        /// <summary>
        /// Execute Query
        /// </summary>
        /// <param name="command">String</param>
        /// <returns>Boolean</returns>
        public Boolean ExecuteNonQuery(String command)
        {
            SqlConnection conn =null;
            try
            {
                conn = this.GetConnection();
                if (conn != null)
                {
                    SqlCommand cmd = new SqlCommand(command, conn);
                    this.MappingParameters(cmd.Parameters, null);
                    cmd.CommandType = CommandType.Text;
                    if (cmd.ExecuteNonQuery() == 0)
                        return false;
                    else
                        return true;

                }
            }
            finally
            {
                CloseConnection(conn);
            }
            return false;
        }

        /// <summary>
        /// Mapping parameter 
        /// </summary>
        /// <param name="paramCollection">SqlParameterCollection</param>
        /// <param name="paramArray">SqlParameter[]</param>
        protected void MappingParameters(SqlParameterCollection paramCollection, SqlParameter[] paramArray)
        {
            if (paramArray != null)
            {
                for (int i = 0; i < paramArray.Length; i++)
                {
                    paramCollection.Add(paramArray[i]);
                }
            }

        }

        /// <summary>
        /// Fill table from store
        /// </summary>
        /// <param name="command">String</param>
        /// <param name="param">SqlParameter[]</param>
        /// <param name="dt">DataTable</param>
        /// <returns>int</returns>
        public void FillStore(String command, SqlParameter[] param, DataTable dt)
        {
            SqlConnection conn = null;
            try
            {
                conn = this.GetConnection();
                if (conn!=null)
                {
                    SqlCommand cmd = new SqlCommand(command, conn);
                    this.MappingParameters(cmd.Parameters, param);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    adapter.Fill(dt);
                }
            }
            finally
            {
                CloseConnection(conn);
            }
        }

        /// <summary>
        /// Fill table from store
        /// </summary>
        /// <param name="command">String</param>
        /// <param name="dt">DataTable</param>
        /// <returns>int</returns>
        public void FillStore(String command, DataTable dt)
        {
            SqlConnection conn = null;
            try
            {
                conn = GetConnection();
                if (conn!=null)
                {
                    SqlCommand cmd = new SqlCommand(command, conn);
                    this.MappingParameters(cmd.Parameters, null);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    adapter.Fill(dt);
                }
            }
            finally
            {
                CloseConnection(conn);
            }
        }

        /// <summary>
        /// Execute from store
        /// </summary>
        /// <param name="command">String</param>
        /// <param name="param">SqlParameter[]</param>
        /// <returns>Boolean</returns>
        public Boolean ExecuteStore(String command, SqlParameter[] param)
        {
            SqlConnection conn = null;
            try
            {
                conn = this.GetConnection();
                if (conn!=null)
                {
                    SqlCommand cmd = new SqlCommand(command, conn);
                    this.MappingParameters(cmd.Parameters, param);
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (cmd.ExecuteNonQuery() != 0)
                        return true;
                }
            }
            finally
            {
                CloseConnection(conn);
            }
            return false;
        }

        /// <summary>
        /// Execute from Store
        /// </summary>
        /// <param name="command">String</param>
        /// <returns>Boolean</returns>
        public Boolean ExecuteStore(String command)
        {
            SqlConnection conn = null;
            try
            {
                if (conn != null)
                {
                    SqlCommand cmd = new SqlCommand(command, conn);
                    this.MappingParameters(cmd.Parameters, null);
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (cmd.ExecuteNonQuery() != 0)
                        return true;

                }
            }
            finally
            {
                CloseConnection(conn);
            }
            return false;
        }

    }
}


